﻿
#region NameSpace
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Security.Permissions;
using System.Security.Policy;
using System.Collections.Generic;
using eOffice;
#endregion

public partial class StoredProcedures
{
    #region constants or configuration items
    const int maxAllowedContiniousWorkingTime =18 * 60;
    const int MaxAllowedGapInOneShift = 4 * 60;
    public static long totalTimeDifference;
    #endregion

    /// <summary>
    /// This function process the raw entries in the TransactionLog
    /// table and generate In Time entries in the dbo.InTime table
    /// </summary>
    /// <remarks>
    /// InTime Status Field Values
    /// 0 -- Only InTime is marked OutTime Marking Pending
    /// 1 -- Out Time marked Duration Not Updated
    /// 2 -- Duration Updated Ready for Further processing
    /// 3 -- In Time processing completed and attendance is updated
    /// Transaction Log Status Field Values
    /// 0 -- New Transaction Log not yet to process
    /// 1 -- Transaction Log processing completed
    /// </remarks>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ProcessLog()
    {
        // Put your code here
      //  RemoveInvalidEntris();
        ProcessTransactionLog();
        ProcessIntime();
    }

    protected static void RemoveInvalidEntris()
    {
        #region Fields
        DataSet transactionLogDS = new DataSet();
         DateTime inTime = Convert.ToDateTime(null);
        DateTime outTime=Convert.ToDateTime(null);
        #endregion
        //Select logs with time difference less than 3 seconds
        transactionLogDS = GetTransactionLogData();
        for (int count = 0; count < transactionLogDS.Tables[0].Rows.Count; count++)
        {
            SqlContext.Pipe.Send("1st Count" + transactionLogDS.Tables[0].Rows.Count.ToString());
            long userId = Convert.ToInt64(transactionLogDS.Tables[0].Rows[count]["trsCardId"].ToString());
            SqlContext.Pipe.Send("UserIs" + userId.ToString());
            DataSet userDS = GetUserDataSetByUniqID(userId);
            for (int userCount = 0; userCount < userDS.Tables[0].Rows.Count; userCount++)
            {
                inTime = Convert.ToDateTime(userDS.Tables[0].Rows[userCount]["trsTimeStamp"].ToString());
                 SqlContext.Pipe.Send("InTime" + inTime.ToString());

                 if (userDS.Tables[0].Rows.Count > userCount + 1)
                 {
                     outTime = Convert.ToDateTime(userDS.Tables[0].Rows[userCount + 1]["trsTimeStamp"].ToString());
                     SqlContext.Pipe.Send("outTime" + outTime.ToString());
                     long tDiff = GetTimeDifferenceInSeconds(inTime, outTime);
                     SqlContext.Pipe.Send("tDiff" + tDiff.ToString());
                     if (tDiff > 0 && tDiff < 5)
                     {
                         SqlContext.Pipe.Send("delete");
                         //Delete Data from TransactionLog
                         DeleteTransactionLogByTransactionTime(userId, outTime);
                     }
                 }
            }
        }

    }
    /// <summary>
    /// This function process the raw entries in the TransactionLog
    /// table and generate In Time entries in the dbo.InTime table    
    /// </summary>
    protected static void ProcessTransactionLog()
    {

        #region Fields
        int count = 0;
        int curInOut = 0;
        int loopCount = 0;
        long curCardID = 0;
        long timeDifference;
        string query = string.Empty;
        DataSet transLogDS;
        DataSet lastInTimeDS;
        DateTime lastInTime;
        DateTime inTime=Convert.ToDateTime(null);
        DateTime outTime = Convert.ToDateTime(null);
        string shiftStartTime = string.Empty;
        string shiftEndTime = string.Empty;
        DataSet shiftDS = new DataSet();
        TimeSpan startTime = TimeSpan.Zero;
        TimeSpan endTime = TimeSpan.Zero;
        #endregion

        //1.Prepare dataset (transLogDS) for all records in Transaction log where status
        //=0 orderby CardID and then by intime
        try
        {
           
            Procedure clsProcedure = new Procedure();
           // SqlContext.Pipe.Send("Start In Transaction!!!!!!");
            transLogDS = new DataSet();
            lastInTimeDS = new DataSet();
            query = "SELECT     trsTransID, trsTimeStamp, trsInOut, trsCardId, trsStatus FROM TransactionLog WHERE (trsStatus = 0) ORDER BY trsCardId, trsTimeStamp";
           // SqlContext.Pipe.Send(query);
            transLogDS = clsProcedure.ExecuteQuery(query);

            //Start a loop for processing above query result
            
            //for (count = 0; count <= transLogDS.Tables[0].Rows.Count; count++)
            //{
            while (loopCount < transLogDS.Tables[0].Rows.Count)
            {

               
                //1. Collect next record from transLogDS dataset
                //2. Collect trsCardID in to curCardID and trsInOut to curInOut 
                SqlContext.Pipe.Send("loop Start");
                //SqlContext.Pipe.Send("For Test : Dhanya");
                curCardID = Convert.ToInt64(transLogDS.Tables[0].Rows[loopCount]["trsCardId"].ToString());
                curInOut = Convert.ToInt32(transLogDS.Tables[0].Rows[loopCount]["trsInOut"].ToString());
                lastInTime = Convert.ToDateTime(transLogDS.Tables[0].Rows[loopCount]["trsTimeStamp"].ToString());
                TimeSpan time = lastInTime.TimeOfDay;
                SqlContext.Pipe.Send(time.ToString());
                //SqlContext.Pipe.Send("Card Id"+curCardID.ToString());
                //SqlContext.Pipe.Send("InOut"+curInOut.ToString());
                //SqlContext.Pipe.Send("InOutTime"+lastInTime.ToString());
                string process = "InTime Insertion!!";
             //   long uniqNumber = InsertintoLogTable(curCardID, process);

                //3. Query InTime table to 'lastInTime' where CardID = curCardID and Status = 0 
                lastInTimeDS = GetLastInTimeByCardId(curCardID);
               
                if (curInOut == 1 && lastInTimeDS.Tables[0].Rows.Count == 0 && curCardID != 0)
                {
                  // string qry="INSERT INTO InTime (CardID,TransDate,Intime,Status) VALUES (" + curCardID + "," + lastInTime.ToShortDateString() + "," + lastInTime + ",0)";


                    DateTime strt = DateTime.Now;
                   // SqlContext.Pipe.Send(lastInTime.ToString());
                    SqlContext.Pipe.Send("Intime Insert");
                    InsertInTimeWithTransactionLogDetails(lastInTime, curCardID);
                    UpdateTransactionLogStatus(curCardID, lastInTime);
                    inTime = lastInTime;
                   // SqlContext.Pipe.Send(uniqNumber.ToString());
                }
                //if (curInOut != 2)
                //{

                //    bool status1 = CheckRecord(curCardID, lastInTime);
                //    if (status1)
                //        totalTimeDifference = 0;
                //}
                totalTimeDifference = 0; ;
                for (count = 0; count < lastInTimeDS.Tables[0].Rows.Count; count++)
                
                {
                    SqlContext.Pipe.Send("last count" + lastInTimeDS.Tables[0].Rows.Count.ToString());
                   // InsertintoLogTable("last count" + lastInTimeDS.Tables[0].Rows.Count.ToString());
                   // DateTime transactionDate = Convert.ToDateTime(lastInTimeDS.Tables[0].Rows[count]["TransDate"].ToString());
                    //4. if curInOut = IN and lastInTime not empty update lastInTime entry in the table 
                    //   with this transaction log deatils . That is update InTime, TransDate etc
                    if (curInOut == 1 && lastInTimeDS.Tables[0].Rows.Count > 0 )
                    {
                        SqlContext.Pipe.Send("Intime Update");
                       // InsertintoLogTable("Intime Update");
                        UpdateInTimeWithTransactionLogDetails(lastInTime, curCardID);
                        UpdateTransactionLogStatus(curCardID, lastInTime);

                        inTime = lastInTime;
                    }
                    //6. if curInOut = IN and lastInTime is empty insert a new entry to InTime
                    //   with details from transaction log deatils and  Continie loop to process next entry

                    //5. If curInOut = OUT and 'LastIntime' is  not empty 
                    if (curInOut == 2 && lastInTimeDS.Tables[0].Rows.Count > 0)
                    {
                        DateTime time1 = Convert.ToDateTime("1/1/0001 12:00:00 AM");
                        //SqlContext.Pipe.Send("tttttttttt~" + time1);
                        //SqlContext.Pipe.Send("innttttttt" + inTime);
                        //if (inTime != Convert.ToDateTime("1/1/0001 12:00:00 AM"))
                        //{
                            inTime = Convert.ToDateTime(lastInTimeDS.Tables[0].Rows[0]["Intime"].ToString());
                            SqlContext.Pipe.Send("trtrtrtrtrt" + inTime);
                       // }


                            SqlContext.Pipe.Send(inTime.ToString());
                           long timeInSeconds = GetTimeDifferenceInSeconds(inTime, lastInTime);
                           if (timeInSeconds >= 0)
                        {
                            timeDifference = GetTimeDifference(inTime, lastInTime);
                                    // SqlContext.Pipe.Send("Time Difference" + timeDifference);
                                    long timeDifference1 = totalTimeDifference + timeDifference;
                                    // SqlContext.Pipe.Send("Time Difference" + timeDifference1);
                                    totalTimeDifference = timeDifference1;


                                    //SqlContext.Pipe.Send("out");                        
                                    //timeDifference = GetTimeDifference(inTime, lastInTime);
                                    //long timeDifference1 = totalTimeDifference + timeDifference;
                                    //totalTimeDifference = timeDifference1;
                                    //if time difference between 'LastIntime'.IntIme and currentTransLog time <= maxAllowedContiniousWorkingTime
                                    SqlContext.Pipe.Send("timeDifference1" + totalTimeDifference.ToString());
                                    SqlContext.Pipe.Send("maxAllowedContiniousWorkingTime" + maxAllowedContiniousWorkingTime.ToString());

                                    if (totalTimeDifference <= maxAllowedContiniousWorkingTime && inTime != Convert.ToDateTime("1/1/0001 12:00:00 AM") && timeDifference >= 0)
                                    {
                                        // SqlContext.Pipe.Send("<<<");
                                        //Update outtime in to 'LastIntime' entry of InTime table with currentTransLog time as
                                        //outTime and set status to 1
                                        UpdateInTime(lastInTime, curCardID);
                                        UpdateInTimeDuration(timeDifference1, curCardID);
                                        UpdateTransactionLogStatus(curCardID, lastInTime);

                                    }
                                    else
                                    {
                                        //Delete the 'LastIntime' entry from IntIme table.
                                        DeleteLastInTime(curCardID, inTime);
                                    }
                                }
                        
                    }
                   // count++;  
                }

                //Chkk time
              //  UpdateLogTable(uniqNumber);
              loopCount++;
            //}
         }
            //Call UpdateInTimeDuration
           
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

   /// <summary>
   /// This function process the entries in the INTIME table and generate
   /// DateWiseAttnData entries. 
   /// </summary>
    protected static void ProcessIntime()
    {
        #region Fields
        DataSet inTimeDS;
        DataSet shiftDetailsDS;
        DataSet totalWorkedMinutesDS;
        DataSet datewiseAttendenceDetailsDS;

        DateTime inTime;
        DateTime shiftStartTime=Convert.ToDateTime(null);
        DateTime shiftEndTime=Convert.ToDateTime(null);
        DateTime nearestShiftDate = Convert.ToDateTime(null);
        DateTime outTime;
        DateTime transDate;
        DateTime presentDateTime;
        DateTime lastOutTime;

        TimeSpan startTime = TimeSpan.Zero;
        TimeSpan endTime = TimeSpan.Zero;
        TimeSpan totWorkDuration = TimeSpan.Zero;

        long recordNumber = 0;
        int loopCount = 0;
        int duration = 0;
        long LatenessMin = 0;
        long cardId = 0;
        int overTime = 0;
        int attendenceCount = 0;
        int totWorkedMin = 0;
        bool overTimeFlag = false;
        int WorkDuration = 0;
        int totDuration=0;
        #endregion

        inTimeDS = new DataSet();
        shiftDetailsDS = new DataSet();
        totalWorkedMinutesDS = new DataSet();
        datewiseAttendenceDetailsDS = new DataSet();
        //lastOuttime = new DataSet();
        inTimeDS = GetInTime();
        SqlContext.Pipe.Send("Process InTimeeeeeeeeeeefdfdf");
        //1. Create a dataset contains InTime entries orderby cardid and then by IN datetime and Status = 2
        //2.Start a loop to process intimes
       // for (int count = 0; count <= inTimeDS.Tables[0].Rows.Count; count++)
       // {
        DataSet lastOuttime = new DataSet();
            while (loopCount < inTimeDS.Tables[0].Rows.Count)
            {
               
                SqlContext.Pipe.Send("while loop");
              //  SqlContext.Pipe.Send("cardID");
                //1. Collect card ID,empID for the current Intime entry
                cardId = Convert.ToInt64(inTimeDS.Tables[0].Rows[loopCount]["CardID"].ToString());
                inTime = Convert.ToDateTime(inTimeDS.Tables[0].Rows[loopCount]["Intime"].ToString());
                duration = Convert.ToInt32(inTimeDS.Tables[0].Rows[loopCount]["Duration"].ToString());
                outTime = Convert.ToDateTime(inTimeDS.Tables[0].Rows[loopCount]["OutTime"].ToString());
                transDate = Convert.ToDateTime(inTimeDS.Tables[0].Rows[loopCount]["TransDate"].ToString());
                //SqlContext.Pipe.Send("CardId"+cardId.ToString());
                //SqlContext.Pipe.Send("inTime" + inTime.ToString());
                //SqlContext.Pipe.Send("duration" + duration.ToString());
                //SqlContext.Pipe.Send("outTime" + outTime.ToString());

                //if (loopCount>=1)
                //    outLastTime = Convert.ToDateTime(inTimeDS.Tables[0].Rows[loopCount - 1]["OutTime"].ToString());
                //else
                //    outLastTime = outTime;
                //2. Collect the shift assignement for that card holder.

                string process = "DateWiseAttnTable!!";
               // long uniqNumber = InsertintoLogTable(cardId, process);

                shiftDetailsDS = GetShiftDetails(cardId);
                if (shiftDetailsDS.Tables[0].Rows.Count > 0)
                {
                    shiftStartTime = Convert.ToDateTime(shiftDetailsDS.Tables[0].Rows[0]["ShStartTime"].ToString());
                    shiftEndTime = Convert.ToDateTime(shiftDetailsDS.Tables[0].Rows[0]["ShEndTime"].ToString());

                    startTime = shiftStartTime.TimeOfDay;
                    endTime = shiftEndTime.TimeOfDay;
                   // SqlContext.Pipe.Send("shift start time" + shiftStartTime.ToString());
                   // SqlContext.Pipe.Send("shift start time" + shiftEndTime.ToString());

                    WorkDuration = Convert.ToInt32(endTime.TotalMinutes - startTime.TotalMinutes);
                  //  SqlContext.Pipe.Send("*******************************" + totWorkDuration);

                    //}
                  //  SqlContext.Pipe.Send(inTime.ToString());
                  //  SqlContext.Pipe.Send(outTime.ToString());
                    //3. Find the nearest shift for the InTime
                    // nearestShiftDate = FindNearestShift(inTime, startTime.ToString(), endTime.ToString());

                    //FindLastOutTime(.......)
                    DataSet lastOutTimeDS = new DataSet();
                    // if (inTimeDS.Tables[0].Rows.Count > 1)
                   // SqlContext.Pipe.Send("check connnnnnnn shiii");
                   // SqlContext.Pipe.Send(inTime.ToString());
                    if (FindLastOutTime(inTime, cardId, out lastOutTimeDS))
                    {
                      //  SqlContext.Pipe.Send("Continuation chkk////");
                      //  SqlContext.Pipe.Send("*******************" + inTime.ToString());
                        if (!CheckForContinuationEntry(lastOutTimeDS, inTime, out nearestShiftDate))
                        {
                          //  SqlContext.Pipe.Send("cuuuuuuu" + nearestShiftDate.ToString());
                            nearestShiftDate = FindNearestShift(nearestShiftDate, startTime.ToString(), endTime.ToString());
                            //SqlContext.Pipe.Send(nearestShiftDate.ToString());
                        }
                    }
                    else
                    {
                       // SqlContext.Pipe.Send("FindNearestShift chkk////");
                       // SqlContext.Pipe.Send("nnnn"+nearestShiftDate.ToString());
                        nearestShiftDate = FindNearestShift(nearestShiftDate, startTime.ToString(), endTime.ToString());
                    }

                  //  SqlContext.Pipe.Send("nearest shift*********" + nearestShiftDate);
                    //4. Check whether there is an enetry present for that NearestShiftDate in DateWiseAttnData for that cardholder
                    //Note: It is important to find entry for the shiftdate ,Not for the Intime date
                    attendenceCount = CheckDatewiseAttendence(cardId, nearestShiftDate);
                  //  SqlContext.Pipe.Send("attendenceCount :" + attendenceCount);
                    //If No
                    if (attendenceCount <= 0)
                    {
                        TimeSpan presentTime = inTime.TimeOfDay;
                        SqlContext.Pipe.Send("presentTime" + presentTime);

                        LatenessMin = GetLatenessMin(inTime, nearestShiftDate, startTime, endTime);
                        // LatenessMin = Convert.ToInt64(presentTime.TotalMinutes - startTime.TotalMinutes);
                        if (LatenessMin <= 0)
                            LatenessMin = 0;
                        overTime = GetOverTime(duration, startTime, endTime, inTime, outTime, nearestShiftDate);
                        if (overTime <= 0)
                            overTime = 0;
                           //int totDuration1 = GetDurationInDateWiseAttnDataByCardId(cardId, nearestShiftDate);
                           if (duration <= maxAllowedContiniousWorkingTime)
                           {
                               //1. Add a new entry in to DateWiseAttn table. Populate following fields
                               //2. TotWorkedMin= IntimeDuration, Status = 0, FirstIntime = InTime.Intime,LatenessMin = InTime-ShiftStartTim(If the value is +ve otherwise zero)
                               //,EmployeeId = DevEmployeeDetails.EmpID (fins out using cardId),LastoutTime = Intime.outTime
                               SqlContext.Pipe.Send("insert datewise attendence table");
                               // totDuration = GetDurationInDateWiseAttnDataByCardId(cardId, nearestShiftDate);
                               // if ((duration + totDuration1) <= maxAllowedContiniousWorkingTime)
                               recordNumber = InsertDateWiseAttendence(cardId, inTime, outTime, duration, LatenessMin, overTime, nearestShiftDate);
                               datewiseAttendenceDetailsDS = GetDatewiseAttendenceDetails(cardId, nearestShiftDate);
                               if (datewiseAttendenceDetailsDS.Tables[0].Rows.Count > 0)
                               {
                                   totWorkedMin = Convert.ToInt32(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtTotWorkedMin"].ToString());
                                   presentDateTime = Convert.ToDateTime(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtFirstInTime"].ToString());
                                   lastOutTime = Convert.ToDateTime(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtLastOutTime"].ToString());
                                   //SqlContext.Pipe.Send("total workedtime" + totWorkedMin);
                                   //SqlContext.Pipe.Send(presentDateTime.ToString());
                                   //SqlContext.Pipe.Send(lastOutTime.ToString());
                               }
                               //3. Update OvertIme = totWorkedMin-ShiftDuartionInMinutes (Update Only if value is +ve, otherwise zero)
                               //4. Update InTime.ConnectedAttnEntry with DateWiseAttnEntry.RecordNo(This is a new field to be added in InTimeTable
                               //5. Update InTime.Status = 3 
                               UpdateInTimeConnectedAttnEntryByCardId(recordNumber, cardId, inTime);
                           }
                    }
                    else
                    {
                         int totDuration1 = GetDurationInDateWiseAttnDataByCardId(cardId, nearestShiftDate);
                         if ((duration + totDuration1) <= maxAllowedContiniousWorkingTime)
                         {
                             //SqlContext.Pipe.Send("else part");
                             totDuration = GetDurationInDateWiseAttnDataByCardId(cardId, nearestShiftDate);
                            // SqlContext.Pipe.Send(totDuration.ToString());
                            // SqlContext.Pipe.Send(duration.ToString());
                             int totDur = duration + totDuration;
                            // SqlContext.Pipe.Send("tot" + totDur.ToString());
                            // SqlContext.Pipe.Send("max" + maxAllowedContiniousWorkingTime.ToString());
                             if ((totDur) < maxAllowedContiniousWorkingTime)
                             {
                                // SqlContext.Pipe.Send("(totDur) < maxAllowedContiniousWorkingTime");
                                 //1. Update the TotWorkTime = TotWorkTime+IntimeDuration
                                 //2. Update lastoutTime = Intime.outTime (after processing the last record it will reflect the 
                                 //correct outtime
                                 UpdateTotalWorkTimeInDatewiseAttn(outTime, duration, cardId, nearestShiftDate);
                                 datewiseAttendenceDetailsDS = GetDatewiseAttendenceDetails(cardId, nearestShiftDate);
                                 totWorkedMin = Convert.ToInt32(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtTotWorkedMin"].ToString());
                                 presentDateTime = Convert.ToDateTime(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtFirstInTime"].ToString());
                                 lastOutTime = Convert.ToDateTime(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtLastOutTime"].ToString());
                                 overTime = GetOverTime(totWorkedMin, startTime, endTime, inTime, outTime, nearestShiftDate);
                             }
                             if (overTime <= 0)
                                 overTime = 0;
                             if (overTime > WorkDuration)
                                 overTimeFlag = true;
                             //3. Update OverTime = totWorkedMin-ShiftDuartionInMinutes (Update Only if value is +ve, otherwise zero)
                             if (overTimeFlag)
                                 UpdateOverTimeinDateWiseAttnByCardId(cardId, nearestShiftDate, overTime);

                             recordNumber = GetRecordNumberByCardId(cardId, nearestShiftDate);
                             //4. Update InTime.ConnectedAttnEntry with DateWiseAttnEntry.RecordNo(This is a new field to be added in InTimeTable
                             //5. Update InTime.Status = 3 
                             UpdateInTimeConnectedAttnEntryByCardId(recordNumber, cardId, inTime);
                             // UpdateDateWiseAttnStatus(recordNumber);
                         }
                    }
                }

                //else
                //{
                //    AutoShiftFind(inTime, outTime, cardId, duration);
                //}
              //  UpdateLogTable(uniqNumber);
                loopCount++;
            }
      // }
    }

    #region Methods

   
    /// <summary>
    /// Write the nearest shift details in a text file
    /// </summary>
    /// <param name="shiftStartTime"></param>
    /// <param name="shiftEndTime"></param>
    /// <param name="nearestShift"></param>
    /// <param name="time"></param>
    /// <summary>
    /// Write the Shift Details in a Text File
    /// </summary>
    protected static void WriteOutPut(string time, string shiftStarttime, string ShiftEndTime, string findShiftDate, string lastShiftDist, string nextShiftDist)
    {
        //StreamWriter writer;
        //if (File.Exists(@"E:\TestFile.txt"))
        //{

        //    writer = File.AppendText("E:\\TestFile.txt");

        //}
        //else
        //{
        //    writer = new StreamWriter("E:\\TestFile.txt");
        //    writer.WriteLine("InTime \tShiftStartTime \tShiftEndTime \tShiftDate \tDistanceFromLastShift \tDistanceFromNextShift");
        //    writer.Write("---------------------------------------------------------------------------------------------------------------");
        //}
        //writer.WriteLine(time + "\t" + shiftStarttime + "\t" + ShiftEndTime + " \t " + findShiftDate + " \t" + lastShiftDist + " \t" + nextShiftDist + "\r\n");
        //writer.Close();
    }

    /// <summary>
    ///  NormalShift
    /// </summary>
    /// <param name="inTime"></param>
    /// <param name="startTime"></param>
    /// <param name="endTime"></param>
    /// <returns></returns>
    protected static DateTime ProcessNormalShift(DateTime inTime, string shiftStartTime, string shiftEndTime)
    {
        #region Fields
        string ShiftbeginTime = string.Empty;
        string inTimeDate = string.Empty;
        string inTimeTime = string.Empty;
        string presentTimeDate = string.Empty;
        TimeSpan startTime = TimeSpan.Zero;
        TimeSpan endTime = TimeSpan.Zero;
        TimeSpan presentTime = TimeSpan.Zero;
        TimeSpan checkAM = TimeSpan.Zero;
        DataSet intimeDS = new DataSet();

        #endregion

        inTimeDate = inTime.Date.ToShortDateString();
        inTimeTime = inTime.TimeOfDay.ToString();
        presentTimeDate = inTimeDate + " " + inTimeTime;
        DateTime shiftTimeStart = Convert.ToDateTime(shiftStartTime);
        DateTime shiftTimeEnd = Convert.ToDateTime(shiftEndTime);
        DateTime presentInTime = Convert.ToDateTime(inTimeTime);
        startTime = shiftTimeStart.TimeOfDay;
        endTime = shiftTimeEnd.TimeOfDay;
        presentTime = presentInTime.TimeOfDay;

        //if inTime is between shiftStartTime and shiftEndtime
        if (presentTime >= startTime && presentTime <= endTime)
        {
            SqlContext.Pipe.Send("f inTime is between shiftStartTime and shiftEndtime");
            ShiftbeginTime = inTimeDate + " " + shiftStartTime;

            return inTime;
        }

        //if InTime < ShiftStart Time check between PreviosDay CurrentDay
        if (presentTime < startTime)
        //InTime is AM
        {
            //Need to prepare shiftTime of Intime's Date and Previous Date 
            //Distance from Previos Shift = MoD Of (inTime - PreviusDay.ShiftEndTime)
            //(Use timeSpan method as both times are with date in it )
            SqlContext.Pipe.Send("InTime < ShiftStart Time check between PreviosDay CurrentDay");
            DateTime CurrentDate = Convert.ToDateTime(inTimeDate);
            DateTime previousDate = CurrentDate.AddDays(-1);
            DateTime previousDateShiftEndTime = previousDate.Add(endTime);
            DateTime currentDayShiftStartTime = CurrentDate.Add(startTime);
            TimeSpan distanceFromLastShift = inTime - previousDateShiftEndTime;
            TimeSpan distanceFromNextShidt = currentDayShiftStartTime - inTime;
           // SqlContext.Pipe.Send("distanceFromLastShift"+distanceFromLastShift.ToString());
          //  SqlContext.Pipe.Send("distanceFromNextShidt" + distanceFromNextShidt.ToString());

            ////Distance from Current Shift = Mod Of (CurrentDay.ShiftStartTime -inTime)
            ////Return the nearest shift based on the above time
            if (distanceFromLastShift < distanceFromNextShidt)
            {
                SqlContext.Pipe.Send("distanceFromLastShift < distanceFromNextShidt");
               // WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, previousDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());
                return previousDate;
            }
            else
            {
                SqlContext.Pipe.Send("Else distanceFromLastShift < distanceFromNextShidt");

               // WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, CurrentDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());
                return CurrentDate;
            }

        }
        else
        {
            SqlContext.Pipe.Send("pm");
            //Difference here is that Instead of Previos Date take nextdays date of Intime
            //Need to prepare shiftTime of Intime's Date and Previous Date 
            //Distance from Previos Shift = MoD Of (inTime - PreviusDay.ShiftEndTime)
            //(Use timeSpan method as both times are with date in it )
            DateTime CurrentDate = Convert.ToDateTime(inTimeDate);
            DateTime nextDate = CurrentDate.AddDays(1);


            DateTime nextDateShiftStartTime = nextDate.Add(startTime);
            DateTime currentDayShiftEndTime = CurrentDate.Add(endTime);

            TimeSpan distanceFromLastShift = inTime - currentDayShiftEndTime;
            TimeSpan distanceFromNextShidt = nextDateShiftStartTime - inTime;
            ////Distance from Current Shift = Mod Of (CurrentDay.ShiftStartTime -inTime)
            ////Return the nearest shift based on the above time
            if (distanceFromLastShift < distanceFromNextShidt)
            {
               // WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, CurrentDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());

                return CurrentDate;
            }
            else
            {
                //WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, nextDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());

                return nextDate;
            }


        }
       
    }

    /// <summary>
    /// Process Overlapped Shift
    /// </summary>
    /// <param name="inTime"></param>
    /// <param name="startTime"></param>
    /// <param name="endTime"></param>
    /// <returns></returns>
    protected static DateTime ProcessOverlapedShift(DateTime inTime, string shiftStartTime, string shiftEndTime)
    {
        #region Fields
        string ShiftbeginTime = string.Empty;
        string inTimeDate = string.Empty;
        string inTimeTime = string.Empty;
        string presentTimeDate = string.Empty;
        TimeSpan startTime = TimeSpan.Zero;
        TimeSpan endTime = TimeSpan.Zero;
        TimeSpan presentTime = TimeSpan.Zero;
        TimeSpan checkAM = TimeSpan.Zero;
        DataSet intimeDS = new DataSet();

        #endregion

        SqlContext.Pipe.Send("inTime" + inTime.ToString());
       //SqlContext.Pipe.Send("shift start" + shiftStartTime.ToString());
       // SqlContext.Pipe.Send("shift end" + shiftEndTime.ToString());
        inTimeDate = inTime.Date.ToShortDateString();
        inTimeTime = inTime.TimeOfDay.ToString();

        presentTimeDate = inTimeDate + " " + inTimeTime;
        DateTime shiftTimeStart = Convert.ToDateTime(shiftStartTime);
        DateTime shiftTimeEnd = Convert.ToDateTime(shiftEndTime);
        DateTime presentInTime = Convert.ToDateTime(inTimeTime);
        startTime = shiftTimeStart.TimeOfDay;
        endTime = shiftTimeEnd.TimeOfDay;
        presentTime = presentInTime.TimeOfDay;
        //Use same logic now let see whether any difference is needed
        //if inTime is between shiftStartTime and shiftEndtime
        SqlContext.Pipe.Send("present time" + presentTime.ToString());
      //  SqlContext.Pipe.Send("start time" + startTime.ToString());
        if (presentTime >= startTime)
        {
            ShiftbeginTime = inTimeDate + " " + shiftStartTime;

            return inTime;
        }
        else
        {
            if (presentTime <= endTime)
            {
                SqlContext.Pipe.Send("presentTime <= endTime");
                DateTime shiftDate = inTime.Subtract(new TimeSpan(1, 0, 0, 0));
                return shiftDate;
            }
        }
        //For overlapped shifts if it not falls in to the previos condition
        //Present time will always greater that shift start time.If it falls 
        //to else condition means it is an error 
        if (presentTime <= startTime)
        //InTime is AM
        {
            SqlContext.Pipe.Send("presentTime <= startTime");
            //Need to prepare shiftTime of Intime's Date and Previous Date 
            //Distance from Previos Shift = MoD Of (inTime - PreviusDay.ShiftEndTime)
            //(Use timeSpan method as both times are with date in it )
            DateTime CurrentDate = Convert.ToDateTime(inTimeDate);
            DateTime previousDate = CurrentDate.AddDays(-1);

            //Since it  overlapped shift endtime will be on current date start time 
            //in previous dat. Similarly start of next shift will also fall in to
            //same date
            DateTime previousDateShiftEndTime = CurrentDate.Add(endTime);
            DateTime currentDayShiftStartTime = CurrentDate.Add(startTime);

            TimeSpan distanceFromLastShift = inTime - previousDateShiftEndTime;
            TimeSpan distanceFromNextShidt = currentDayShiftStartTime - inTime;

            //SqlContext.Pipe.Send("intime" + inTime.ToString());
            //SqlContext.Pipe.Send("previousDateShiftEndTime" + previousDateShiftEndTime.ToString());
            //SqlContext.Pipe.Send("distanceFromLastShift"+distanceFromLastShift.ToString());
            //SqlContext.Pipe.Send("currentDayShiftStartTime" + currentDayShiftStartTime.ToString());
            //SqlContext.Pipe.Send("distanceFromNextShidt"+distanceFromNextShidt.ToString());

            ////Distance from Current Shift = Mod Of (CurrentDay.ShiftStartTime -inTime)
            ////Return the nearest shift based on the above time

            if (distanceFromLastShift < distanceFromNextShidt)
            {
                SqlContext.Pipe.Send(previousDate.ToString());
                //WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, previousDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());
                return previousDate;
            }
            else
            {
                SqlContext.Pipe.Send("current"+CurrentDate.ToString());
                //  WriteOutPut(inTime.ToString(), shiftStartTime, shiftEndTime, CurrentDate.ToLongDateString(), distanceFromLastShift.ToString(), distanceFromNextShidt.ToString());
                return CurrentDate;
            }
        }
        else
        {

            throw new Exception("In overlapped shift preset time > start time condition occured");
        }
    }

    /// <summary>
    /// Updates Duration Field in Intime table status fiels is 1. 
    /// Changed the record status to 2
    /// </summary>
    protected static void UpdateInTimeDuration(long duration, long curCardID)
    {
        #region Fields
        int count = 0;
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    InTime SET   Duration='" + duration + "',Status=2 WHERE  Status=1 and CardID='" + curCardID + "'";
           // SqlContext.Pipe.Send(query.ToString());
            count = clsProcedure.ExecuteNonQuery(query);
            
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Get the intime table details where status is zero
    /// </summary>
    /// <returns></returns>
    protected static DataSet GetInTime()
    {
        #region Fields
        string query = string.Empty;
        DataSet inTimeDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT CardID,Intime, OutTime, Duration,TransDate FROM InTime WHERE Status = 2 ORDER BY CardID,Intime";
           // SqlContext.Pipe.Send(query);
            inTimeDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return inTimeDS;
    }

    /// <summary>
    /// Get shift details by cardId
    /// </summary>
    /// <param name="cardId"></param>
    /// <returns></returns>
    protected static DataSet GetShiftDetails(long cardId)
    {
        #region Fields
        string query = string.Empty;
        DataSet shiftDetailsDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT ShStartTime,ShEndTime  FROM ShiftDetailsCardsView where EmpId='" + cardId + "'";
           // SqlContext.Pipe.Send(query);
            shiftDetailsDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return shiftDetailsDS;
       
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    /// <returns></returns>
    protected static int CheckDatewiseAttendence(long cardId,DateTime inTime)
    {
        #region Fields
        string query = string.Empty;
        int recCount = 0;
        DataSet shiftDetailsDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            SqlContext.Pipe.Send("chk date wise attendence");
            query = "SELECT     dtDate FROM DateWiseAttnData WHERE dtEmpID='" + cardId + "' and (Convert(varchar,dtDate,101)=Convert(varchar,cast('" + inTime + "' as datetime),101))";
            //SqlContext.Pipe.Send(query);
            shiftDetailsDS = clsProcedure.ExecuteQuery(query);
            recCount = shiftDetailsDS.Tables[0].Rows.Count;
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return recCount;
    }
 
    /// <summary>
    /// Insert data in to datewiseattn table
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    /// <param name="outTime"></param>
    /// <param name="duration"></param>
    /// <param name="lateness"></param>
    /// <param name="overTime"></param>
    /// <returns></returns>
    protected static long InsertDateWiseAttendence(long cardId, DateTime inTime, DateTime outTime, int duration, long lateness, int overTime, DateTime nearestShiftDate)
    {
        #region Fields
        object recordNo;
        string query;
        long recNo = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "INSERT INTO DateWiseAttnData(dtEmpID,dtDate,dtFirstInTime,dtStatus,dtLastOutTime,dtLatenessMin,dtHours,dtMinutes,dtTotWorkedMin,dtOverTimeMin) VALUES     ('" + cardId + "','" + nearestShiftDate.ToShortDateString() + "','" + inTime + "','0','" + outTime + "','" + lateness + "','" + duration / 60 + "','" + duration % 60 + "','" + duration + "','" + overTime + "')" + "Select Scope_Identity()";
            recordNo = clsProcedure.ExecuteScalar(query);
            recNo = Convert.ToInt64(recordNo);
           // SqlContext.Pipe.Send(query);
            SqlContext.Pipe.Send("No of rows Inserted(DatewiseAttendence table)" + ":1");
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return recNo;
    }

    /// <summary>
    /// Get overtime
    /// </summary>
    /// <param name="totalWorkedTime"></param>
    /// <param name="shiftStartTime"></param>
    /// <param name="shiftEndTime"></param>
    /// <returns></returns>
    protected static DataSet GetDatewiseAttendenceDetails(long cardId,DateTime nearestShift)
    {
        #region Fields
        string query = string.Empty;
        DataSet datewiseAttendenceDetailsDS = new DataSet(); 
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT  dtTotWorkedMin,dtFirstInTime,dtLastOutTime  FROM      DateWiseAttnData  WHERE dtEmpID='" + cardId + "' AND  convert(varchar,dtDate,101)=convert(varchar,cast('" + nearestShift + "' as datetime),101)";
           // SqlContext.Pipe.Send("qqqqqqqqqqqqqqqq");
            //SqlContext.Pipe.Send(query);
            datewiseAttendenceDetailsDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return datewiseAttendenceDetailsDS;
    }

    /// <summary>
    /// Update the status and record number in InTime
    /// </summary>
    /// <param name="recordNo"></param>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    protected static void UpdateInTimeConnectedAttnEntryByCardId(long recordNo,long cardId,DateTime inTime)
    {
        #region Fields
        string query;
        int count = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    InTime SET  Status=3 ,ConnectedAttnEntry='" + recordNo + "' WHERE CardID='" + cardId + "' and Intime='" + inTime + "' and Status=2";
            count = clsProcedure.ExecuteNonQuery(query);
           // SqlContext.Pipe.Send("No of rows Updated(InTime table)" + ":1");
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Update totalworkedTime in datewiseattn table
    /// </summary>
    /// <param name="outTime"></param>
    /// <param name="duration"></param>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    protected static void UpdateTotalWorkTimeInDatewiseAttn(DateTime outTime,int duration,long cardId,DateTime inTime)
    {
        #region Fields
        string query;
        int count = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    DateWiseAttnData SET  dtLastOutTime='" + outTime + "',dtTotWorkedMin=dtTotWorkedMin+'" + duration + "',dtHours=(dtTotWorkedMin+'" + duration + "')/60,dtMinutes=(dtTotWorkedMin+'" + duration + "')%60 WHERE dtEmpID='" + cardId + "' and  dtStatus=0 and Convert(varchar,dtDate,101)=Convert(varchar,cast('" + inTime + "' as datetime),101)";
          //  SqlContext.Pipe.Send(query);
            //Convert(varchar,dtDate,101)=Convert(varchar,cast('"+ inTime +"' as datetime),101)";
            count = clsProcedure.ExecuteNonQuery(query);
           // SqlContext.Pipe.Send("No of rows Updated(DateWiseAttn table)" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }


    protected static int GetDurationInDateWiseAttnDataByCardId(long cardId, DateTime inTime)
    {
        #region Fields
        object duration;
        string query;
        int durationTime = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT  dtTotWorkedMin FROM  DateWiseAttnData WHERE dtEmpID='" + cardId + "' and Convert(varchar,dtDate,101)=Convert(varchar,cast('" + inTime + "' as datetime),101)";
            duration = clsProcedure.ExecuteScalar(query);
            durationTime = Convert.ToInt32(duration);
           // SqlContext.Pipe.Send(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return durationTime;
    }
    /// <summary>
    /// Update overtime
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    /// <param name="overTime"></param>
    protected static void UpdateOverTimeinDateWiseAttnByCardId(long cardId, DateTime inTime,int overTime)
    {
        #region Fields
        string query;
        int count = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    DateWiseAttnData SET  dtOverTimeMin='" + overTime + "' WHERE dtEmpID='" + cardId + "' and Convert(varchar,dtDate,101)=Convert(varchar,cast('" + inTime + "' as datetime),101)";
            count = clsProcedure.ExecuteNonQuery(query);
           // SqlContext.Pipe.Send("No of rows Updated(DateWiseAttn table)" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Get record number
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    /// <returns></returns>
    protected static long GetRecordNumberByCardId(long cardId,DateTime inTime)
    {
        #region Fields
        object recordNo;
        string query;
        long recNo = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT  RecordNo FROM  DateWiseAttnData WHERE dtEmpID='" + cardId + "' and Convert(varchar,dtDate,101)=Convert(varchar,cast('" + inTime + "' as datetime),101)";
            recordNo = clsProcedure.ExecuteScalar(query);
            recNo = Convert.ToInt64(recordNo);
            SqlContext.Pipe.Send(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return recNo;
    }

    protected static bool CheckRecord(long cardId, SqlDateTime date)
    {
        bool status;
        string query = string.Empty;

        Procedure clsProcedure = new Procedure();
        query = "SELECT Intime, OutTime, Duration FROM InTime WHERE convert(varchar,TransDate,103)=convert(varchar,cast('" + date + "' as datetime),103) and CardID='" + cardId + "'";
        DataSet lastInTimeDS = clsProcedure.ExecuteQuery(query);
        if (lastInTimeDS.Tables[0].Rows.Count > 0)
            status = false;
        else
            status = true;
        return status;

    }
    /// <summary>
    /// Get LastInTime by CardID
    /// </summary>
    /// <param name="curCardID"></param>
    /// <returns></returns>
    protected static DataSet GetLastInTimeByCardId(long curCardID)
    {
        #region Fields
        string query = string.Empty;
        DataSet lastInTimeDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "SELECT Intime, OutTime, Duration FROM InTime WHERE (Status = 0) and CardID=" + curCardID + "";
            //InsertintoLogTable(query);
            SqlContext.Pipe.Send(query);
            lastInTimeDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
        }
        return lastInTimeDS;
    }

    /// <summary>
    /// Update InTime with TransactionLogDetails
    /// </summary>
    /// <param name="lastInTime"></param>
    /// <param name="curCardID"></param>
    protected static void UpdateInTimeWithTransactionLogDetails(SqlDateTime lastInTime, long curCardID)
    {
        #region Fields
        int count = 0;
        string query = string.Empty;
        string query1 = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    InTime SET Intime='" + lastInTime + "'  WHERE CardID='" + curCardID + "' and Status=0";
          //  query1 = "UPDATE    InTime SET Intime=" + lastInTime + "  WHERE CardID=" + curCardID + " and Status=0";
           // InsertintoLogTable(query1);
            SqlContext.Pipe.Send("fdfdf"+query);
            count = clsProcedure.ExecuteNonQuery(query);
            SqlContext.Pipe.Send("No of rows Update " + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
            
        }
    }

    /// <summary>
    /// Insert InTime
    /// </summary>
    /// <param name="lastInTime"></param>
    /// <param name="cardId"></param>
    protected static void InsertInTimeWithTransactionLogDetails(DateTime lastInTime, long cardId)
    {
        #region Fields
        int count = 0;
        string query = string.Empty;
        string query1 = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "INSERT INTO InTime (CardID,TransDate,Intime,Status) VALUES ('" + cardId + "','" + lastInTime.ToShortDateString() + "','" + lastInTime + "',0)";
            SqlContext.Pipe.Send(query);
            //query1 = "INSERT INTO InTime (CardID,TransDate,Intime,Status) VALUES (" + cardId + "," + lastInTime.ToShortDateString() + "," + lastInTime + ",0)";

            // InsertintoLogTable(query1);
            count = clsProcedure.ExecuteNonQuery(query);
            SqlContext.Pipe.Send(query);
            //SqlContext.Pipe.Send("No of rows Inserted" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Get the timedifference b/w Intime and outtime
    /// </summary>
    /// <param name="inTime"></param>
    /// <param name="timeStamp"></param>
    /// <returns></returns>
    protected static long GetTimeDifference(DateTime inTime, DateTime timeStamp)
    {
        #region Fields
        string query = string.Empty; ;
        long diff = 0;
        DataSet timeDifferenceDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
           // SqlContext.Pipe.Send("InTime" + inTime.ToString());
           // SqlContext.Pipe.Send("InTime" + timeStamp.ToString());
            if (inTime.ToString() != "1/1/0001 12:00:00 AM")
            {
                query = "select datediff(mi,'" + inTime + "','" + timeStamp + "') as Diff";
                // SqlContext.Pipe.Send("time difference");
                SqlContext.Pipe.Send(query);
                timeDifferenceDS = clsProcedure.ExecuteQuery(query);
                diff = Convert.ToInt64(timeDifferenceDS.Tables[0].Rows[0][0].ToString());
            }
            
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return diff;
    }

    protected static long GetTimeDifferenceInSeconds(DateTime inTime, DateTime timeStamp)
    {
        #region Fields
        string query = string.Empty; ;
        long diff = 0;
        DataSet timeDifferenceDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            // SqlContext.Pipe.Send("InTime" + inTime.ToString());
            // SqlContext.Pipe.Send("InTime" + timeStamp.ToString());
            if (inTime.ToString() != "1/1/0001 12:00:00 AM")
            {
                query = "select datediff(s,'" + inTime + "','" + timeStamp + "') as Diff";
                // SqlContext.Pipe.Send("time difference");
                SqlContext.Pipe.Send(query);
                timeDifferenceDS = clsProcedure.ExecuteQuery(query);
                diff = Convert.ToInt64(timeDifferenceDS.Tables[0].Rows[0][0].ToString());
            }

        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return diff;
    }
   
    /// <summary>
    /// Update InTime
    /// </summary>
    /// <param name="outTime"></param>
    /// <param name="curCardID"></param>
    protected static void UpdateInTime(SqlDateTime outTime, long curCardID)
    {
        #region Fields
        int count = 0;
        string query;
        string query1=string.Empty;

        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    InTime SET   OutTime='" + outTime + "',Status=1 WHERE  CardID='" + curCardID + "' and Status=0 ";
            query1 = "UPDATE    InTime SET   OutTime=" + outTime + ",Status=1 WHERE  CardID=" + curCardID + " and Status=0 ";
           // InsertintoLogTable(query1);
           // SqlContext.Pipe.Send(query);
            //and convert(varchar,TransDate,101)=convert(varchar,cast('" + outTime + "' as datetime),101)";
            count = clsProcedure.ExecuteNonQuery(query);
            SqlContext.Pipe.Send(query);
            //SqlContext.Pipe.Send("No of rows Updated(Update OutTime)" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// DeleteLastInTime
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="inTime"></param>
    protected static void DeleteLastInTime(long cardId, SqlDateTime inTime)
    {
        #region Fields
        int count = 0;
        string query;
        string query1=string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "DELETE FROM InTime WHERE CardID='" + cardId + "' and  InTime='" + inTime + "'";
            query1 = "DELETE FROM InTime WHERE CardID=" + cardId + " and  InTime=" + inTime + "";
           // InsertintoLogTable(query1);
            SqlContext.Pipe.Send(query);
            count = clsProcedure.ExecuteNonQuery(query);
           // SqlContext.Pipe.Send("No of rows Deleted" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
        }
    }


    /// <summary>
    /// Find out the nearest shift for the given inTime
    /// Returns the startTime of the shift with date
    /// </summary>
    /// <param name="inTime">Start time of an InTime slot in the date time format eg:- 06/17/2009 12:15 AM</param>
    /// <param name="shiftStartTime"> time part only valid eg: 6AM </param>
    /// <param name="shiftEndtime"> time part only is valid eg: 2PM</param>
    /// <returns>Begin Time of the shift with valid date eg : 06/17/2009 6:00 AM</returns>
    /// 
    protected static DateTime FindNearestShift(DateTime inTime, string shiftStartTime, string shiftEndTime)
    {
        #region Fields
        TimeSpan startTime = TimeSpan.Zero;
        TimeSpan endTime = TimeSpan.Zero;
        DateTime nearestShift;
        #endregion

        DateTime shiftTimeStart = Convert.ToDateTime(shiftStartTime);
        DateTime shiftTimeEnd = Convert.ToDateTime(shiftEndTime);
        startTime = shiftTimeStart.TimeOfDay;
        endTime = shiftTimeEnd.TimeOfDay;
       // SqlContext.Pipe.Send("startTime" + startTime.ToString());
        //SqlContext.Pipe.Send("endTime" + endTime.ToString());

        if (startTime < endTime)
        //Check whether shift is an overlaped on (means spread between 2 days,starting on PM and ending on AM
        //NormalShift
        {
            SqlContext.Pipe.Send("normal shift");
            nearestShift = ProcessNormalShift(inTime, shiftStartTime, shiftEndTime);
            return nearestShift;

        }
        else
        //OverLapped Shift
        {
            SqlContext.Pipe.Send("overlapped shift");
            nearestShift = ProcessOverlapedShift(inTime, shiftStartTime, shiftEndTime);
            return nearestShift;

        }
    }

    /// <summary>
    /// Get OverTime (OvertIme = totWorkedMin-ShiftDuartionInMinutes )
    /// </summary>
    /// <param name="totalWorkedTime"></param>
    /// <param name="shiftStartTime"></param>
    /// <param name="shiftEndTime"></param>
    /// <param name="intime"></param>
    /// <param name="outTime"></param>
    /// <param name="nearestshiftdate"></param>
    /// <returns></returns>
    protected static int GetOverTime(int totalWorkedTime, TimeSpan shiftStartTime, TimeSpan shiftEndTime, DateTime intime, DateTime outTime,DateTime nearestshiftdate)
    {
        #region Fields
        int OvertIme = 0;
        TimeSpan duration = TimeSpan.Zero;
        TimeSpan checkAM = TimeSpan.Zero;
        DateTime date2;
        int totShiftDuration = 0;
        #endregion

        SqlContext.Pipe.Send("overtime");
        DateTime presenttime = Convert.ToDateTime(nearestshiftdate.ToShortDateString());
        TimeSpan startTime = intime.TimeOfDay;
        DateTime endtime = Convert.ToDateTime(outTime.ToShortDateString());
        DateTime date1 = presenttime.Add(shiftStartTime);
        DateTime check = DateTime.Parse("12:00");
        checkAM = check.TimeOfDay;
        SqlContext.Pipe.Send("date1" + date1.ToString());
        if ((shiftStartTime > checkAM && shiftEndTime < checkAM) || (shiftStartTime < checkAM && shiftEndTime < checkAM))
        {
            date2 = date1.AddDays(1);
            DateTime shiftEnd = Convert.ToDateTime(date2.ToShortDateString());
            date2 = shiftEnd.Add(shiftEndTime);
           // SqlContext.Pipe.Send("date2" + date2.ToString());

        }
        else
        {
            date2 = presenttime.Add(shiftEndTime);
            SqlContext.Pipe.Send("date2222" + date2.ToString());

        }
            duration = date2 - date1;
          //  SqlContext.Pipe.Send("duration" + duration.ToString());
            totShiftDuration = Convert.ToInt32(duration.TotalMinutes);
          //  SqlContext.Pipe.Send(totShiftDuration.ToString());
            OvertIme = totalWorkedTime - totShiftDuration;
            return OvertIme;
    }

    /// <summary>
    /// Get Lateness
    /// </summary>
    /// <param name="inTime"></param>
    /// <param name="nearestShiftDate"></param>
    /// <param name="shiftStartTime"></param>
    /// <param name="shiftEndTime"></param>
    /// <returns></returns>
    protected static long GetLatenessMin(DateTime inTime,DateTime nearestShiftDate, TimeSpan shiftStartTime, TimeSpan shiftEndTime)
    {
        #region Fields
        long latenessInMinutes = 0;
        #endregion
        TimeSpan presentInTime = inTime.TimeOfDay;
        DateTime nearestShift = Convert.ToDateTime(nearestShiftDate.ToShortDateString());
        DateTime endtime = Convert.ToDateTime(inTime.ToShortDateString());

        DateTime date1 = nearestShift.Add(shiftStartTime);
        SqlContext.Pipe.Send("lateness");
        SqlContext.Pipe.Send("date1" + date1.ToString());
        SqlContext.Pipe.Send("intime" + inTime.ToString());

        TimeSpan obj = inTime.Subtract(date1);
        SqlContext.Pipe.Send(obj.ToString());
        latenessInMinutes = Convert.ToInt32(obj.TotalMinutes);
        SqlContext.Pipe.Send(latenessInMinutes.ToString());
        return latenessInMinutes;
    }



    /// <summary>
    /// This function find out the nearest lower InTime table entry for the
    /// given INTIME
    /// </summary>
    /// <param name="currentInTime"></param>
    /// <param name="cardID"></param>
    /// <param name="outTimeDS"></param>
    /// <returns></returns>
    /// <remarks>If the dataset which is used to process Intime is passed to this function
    /// then in most cases we can avoid a query. This can be considered for optimisation.</remarks>
     protected static bool FindLastOutTime(SqlDateTime currentInTime, long cardID, out DataSet outTimeDS)
    {
        #region Fields
        Procedure clsProcedure = new Procedure();
        DataSet outTimeDetailsDS = new DataSet();
        string query = string.Empty;
        #endregion

        try
        {
            query = "select  top 1 * from InTime where InTime.outTime<='" + currentInTime + "'  and InTime.cardID ='" + cardID + "' order by InTime.outTime desc";
            SqlContext.Pipe.Send(query);
            outTimeDetailsDS = clsProcedure.ExecuteQuery(query);
            SqlContext.Pipe.Send(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
            outTimeDS = null;
            SqlContext.Pipe.Send("return false");
            return false;
        }
        outTimeDS = outTimeDetailsDS;
        SqlContext.Pipe.Send("return true");

        return true;
    }


    /// <summary>
    /// This function checks whether current Intime entry is continuation of last processed entry
    /// That means current entry caused by employee taking a break in the same shift
    /// If the Gap between lastOut and CurrentIn is less than a set time (MaxAllowedGapInOneShift: Say 4 hours) then it is treated
    /// as a continiuation. Even if it falls in to another shift's time it will be considerd as
    /// continuation. This function to be called before Finding nearest Shift and processing
    /// records based on that
    /// <param name="lastoutTime"></param>
    /// <param name="currentInTime"></param>
    protected static bool CheckForContinuationEntry(DataSet lastOutTimeDS, DateTime currentInTime, out DateTime shiftDate)
    {
        #region Fields
        DateTime outTime = Convert.ToDateTime(null);
        DateTime inTime = Convert.ToDateTime(null);
        DateTime shiftDateTime = Convert.ToDateTime(null);
        int recordNo = 0;
        TimeSpan gap = TimeSpan.Zero;

        #endregion

        SqlContext.Pipe.Send("Continuation chk");
        if (lastOutTimeDS.Tables[0].Rows.Count > 0)
        {
            outTime = Convert.ToDateTime(lastOutTimeDS.Tables[0].Rows[0]["outTime"].ToString());
            inTime = Convert.ToDateTime(lastOutTimeDS.Tables[0].Rows[0]["Intime"].ToString());
            //SqlContext.Pipe.Send("CurrentTimeInnnnnnnnnn" + currentInTime.ToString());
            //SqlContext.Pipe.Send("OutTimeeeeeeeeeeee" + outTime.ToString());
            //SqlContext.Pipe.Send("llll" + lastOutTimeDS.Tables[0].Rows[0]["ConnectedAttnEntry"].ToString());
            gap = currentInTime - outTime;
            SqlContext.Pipe.Send("gppppppp" + gap);
            if (lastOutTimeDS.Tables[0].Rows[0]["ConnectedAttnEntry"].ToString() != "") 
            recordNo = Convert.ToInt32(lastOutTimeDS.Tables[0].Rows[0]["ConnectedAttnEntry"].ToString());
            //SqlContext.Pipe.Send("recccc" + recordNo);
            //SqlContext.Pipe.Send(outTime.ToString());
            //SqlContext.Pipe.Send(inTime.ToString());
            //SqlContext.Pipe.Send("gap in minutes" + gap.ToString());
        }
        int gapInMinutes = Convert.ToInt32(gap.TotalMinutes);
       // SqlContext.Pipe.Send("gapInMinutes" + gapInMinutes);
       // SqlContext.Pipe.Send("MaxAllowedGapInOneShift" + MaxAllowedGapInOneShift);

        if (gapInMinutes < MaxAllowedGapInOneShift && recordNo!=0)
        {

            //select dtDate from Datewiseattndate where recordNo = lastOutTime.Tables[0].Rows[0][ConnectedAttnEntry]
            DataSet dateDS = GetNearestShiftDSateInDatewiseAttn(recordNo);
            if (dateDS.Tables[0].Rows.Count > 0)
                shiftDateTime = Convert.ToDateTime(dateDS.Tables[0].Rows[0]["dtDate"].ToString());
            else
            {
                shiftDate = currentInTime;
              //  SqlContext.Pipe.Send("ggggggggg" + shiftDate.ToString());

                return false;
            }
            //Return it as the shiftDate
            shiftDate = shiftDateTime;
           // SqlContext.Pipe.Send("ggggggggg" + shiftDate.ToString());

            return true;
        }
        else
        {
            shiftDate = currentInTime;
           // SqlContext.Pipe.Send("ggggggggg"+shiftDate.ToString());

            return false;
        }
    }
    /// <summary>
    /// GetInTime
    /// </summary>
    /// <param name="lastOutTime"></param>
    /// <param name="cardId"></param>
    /// <returns></returns>
    protected static DataSet GetInTime(string lastOutTime, long cardId)
    {
        #region Fields
        DataSet inTimeDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        string query = string.Empty;
        #endregion
        try
        {
            query = "SELECT    Intime,ConnectedAttnEntry  FROM    InTime where OutTime='" + lastOutTime + "' and CardID='" + cardId + "'";
            inTimeDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return inTimeDS;
    }

    /// <summary>
    /// GetNearestShiftDSateInDatewiseAttn
    /// </summary>
    /// <param name="recordNo"></param>
    /// <returns></returns>
    protected static DataSet GetNearestShiftDSateInDatewiseAttn(int recordNo)
    {
        #region Fields
        Procedure clsProcedure = new Procedure();
        DateTime date = Convert.ToDateTime(null);
        DataSet dateDS = new DataSet();
        string query = string.Empty;
        #endregion
        try
        {
            query = "select dtDate from DateWiseAttnData where recordNo ='" + recordNo + "'";
            SqlContext.Pipe.Send(query);
            dateDS = clsProcedure.ExecuteQuery(query);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        } return dateDS;
    }

    /// <summary>
    /// Update TransactionLog Status
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="trsTimeStamp"></param>
    protected static void UpdateTransactionLogStatus(long cardId,DateTime trsTimeStamp)
    {
        #region Fields
        int count = 0;
        string query = string.Empty;
        string query1 = string.Empty;

        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    TransactionLog SET       trsStatus=1 where trsCardId='" + cardId + "' and trsTimeStamp= '" + trsTimeStamp + "'  ";
          //  query1 = "UPDATE    TransactionLog SET       trsStatus=1 where trsCardId=" + cardId + " and trsTimeStamp= " + trsTimeStamp + "";
           // InsertintoLogTable(query1);
            count = clsProcedure.ExecuteNonQuery(query);
            SqlContext.Pipe.Send(query);
            SqlContext.Pipe.Send("No of rows Updated" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
           // InsertintoLogTable("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Update DatewiseAttnStatus
    /// </summary>
    /// <param name="recordNumber"></param>
    protected static void UpdateDateWiseAttnStatus(long recordNumber)
    {
        #region Fields
        int count = 0;
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    DateWiseAttnData SET  dtStatus=1  WHERE RecordNo='"+recordNumber+"'";
            count = clsProcedure.ExecuteNonQuery(query);
            SqlContext.Pipe.Send(query);
            SqlContext.Pipe.Send("No of rows Updated" + ":" + count);
        }
        catch (SqlException ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// Get LastOutTime by EmployeeId
    /// </summary>
    /// <param name="empId"></param>
    /// <returns></returns>
    protected static DataSet GetLastOutTimeByEmployeeId(long empId)
    {
        #region Fields
        string query = string.Empty;
        DataSet lastOutTimeDS = new DataSet();
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "select dtLastOutTime,dtDate,dtTotWorkedMin,dtOverTimeMin from DateWiseAttnData where dtEmpID='" + empId + "' and dtLastOutTime IS NOT NULL order by dtLastOutTime desc";
            //  SqlContext.Pipe.Send(query);
            lastOutTimeDS = clsProcedure.ExecuteQuery(query);
        }
        catch (Exception ex)
        {
             SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return lastOutTimeDS;

    }

    /// <summary>
    /// Find AutoShift add details to nearest shift of the Employee
    /// </summary>
    /// <param name="curInTime"></param>
    /// <param name="curOutTime"></param>
    /// <param name="empId"></param>
    /// <param name="duration"></param>
  protected static  void AutoShiftFind(DateTime curInTime, DateTime curOutTime, long empId, int duration)
    {
        #region Fields
        int maxAllowedEarliness = 120;
        DataSet lastOutTimeDS = new DataSet();
        DateTime lastOutTime;
        TimeSpan gap = TimeSpan.Zero;
        List<DateTime> _cutShiftTimes = new List<DateTime>();
        long gapInMinutes = 0;
        TimeSpan presentTime = TimeSpan.Zero;
        TimeSpan shiftStartTime = TimeSpan.Zero;
        TimeSpan shiftEndTime = TimeSpan.Zero;
        int recCount = 0;
        long LatenessMin = 0;
        long recordNumber = 0;
        int overTime = 0;
        DateTime nearestShiftDate = Convert.ToDateTime(null);
        DataSet datewiseAttendenceDetailsDS = new DataSet();
        int totWorkedMin = 0;
        int timeinMinutes = 0;
        #endregion

        presentTime = curInTime.TimeOfDay;
        lastOutTimeDS = GetLastOutTimeByEmployeeId(empId);
        if (lastOutTimeDS.Tables[0].Rows.Count > 0)
        {
            lastOutTime = Convert.ToDateTime(lastOutTimeDS.Tables[0].Rows[0]["dtLastOutTime"].ToString());
            nearestShiftDate = Convert.ToDateTime(lastOutTimeDS.Tables[0].Rows[0]["dtDate"].ToString());
            totWorkedMin = Convert.ToInt32(lastOutTimeDS.Tables[0].Rows[0]["dtTotWorkedMin"].ToString());
            timeinMinutes = Convert.ToInt32(lastOutTimeDS.Tables[0].Rows[0]["dtOverTimeMin"].ToString()); 
            gap = curInTime - lastOutTime;
            gapInMinutes = Convert.ToInt64(gap.TotalMinutes);
            if (gapInMinutes < 0)
                gapInMinutes = 0;

        }
        if (gapInMinutes != 0 && gapInMinutes < MaxAllowedGapInOneShift)
        {
            TimeSpan workDuration = curOutTime - curInTime;
            overTime = Convert.ToInt32(workDuration.TotalMinutes);
            if (overTime <= 0)
                overTime = 0;

            UpdateOverTimeByCardID(empId, nearestShiftDate, overTime + timeinMinutes);
            UpdateTotalWorkTimeInDatewiseAttn(curOutTime, duration, empId, nearestShiftDate);

        }

        else
        {
            _cutShiftTimes = PrepareShiftTimes(curInTime, empId);
            //start a loop for checking in _cutShiftTimes collection
            for (int count = 0; count < _cutShiftTimes.Count; count = count + 2)
            {

                shiftStartTime = _cutShiftTimes[count].TimeOfDay;
                shiftEndTime = _cutShiftTimes[count + 1].TimeOfDay;
                TimeSpan allowedEarliness = shiftStartTime - presentTime;
                int earliness = Convert.ToInt32(allowedEarliness.TotalMinutes);
                //If  curInTime is between StartTime and EndTime of curShift
                if (presentTime >= shiftStartTime && presentTime <= shiftEndTime)
                {
                    LatenessMin = GetLatenessMin(curInTime, _cutShiftTimes[count], shiftStartTime, shiftEndTime);
                    // LatenessMin = Convert.ToInt64(presentTime.TotalMinutes - startTime.TotalMinutes);
                    if (LatenessMin <= 0)
                        LatenessMin = 0;

                    recCount = CheckDateWiseAttnData(_cutShiftTimes[count], empId);
                    if (recCount <= 0)
                    //Create datewiseAttendance entry if not exist
                    {
                        overTime = GetOverTime(duration , shiftStartTime, shiftEndTime, curInTime, curOutTime, _cutShiftTimes[count]);
                        if (overTime <= 0)
                            overTime = 0;

                        recordNumber = InsertDateWiseAttendenceEntry(empId, curInTime, curOutTime, LatenessMin, _cutShiftTimes[count], duration, overTime);
                    }
                    //Update FirstIntime and lateness if the entry is created newly
                    else
                    {
                        overTime = GetOverTime(duration + totWorkedMin, shiftStartTime, shiftEndTime, curInTime, curOutTime, _cutShiftTimes[count]);
                        if (overTime <= 0)
                            overTime = 0;

                        datewiseAttendenceDetailsDS = GetDatewiseAttendenceDetails(empId, _cutShiftTimes[count]);
                        totWorkedMin = Convert.ToInt32(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtTotWorkedMin"].ToString());
                        UpdateFirstInTimeInDateWiseAttnData(curInTime, empId, _cutShiftTimes[count]);
                    }
                    //Update workedHours
                    //Update shiftId in the table (new field)

                    UpdateInTimeConnectedAttnEntryByCardId(recordNumber, empId, curInTime);
                    //break the loop
                    break;
                }
                else if ((presentTime < shiftStartTime) && (earliness < maxAllowedEarliness))
                {
                    LatenessMin = GetLatenessMin(curInTime, _cutShiftTimes[count], shiftStartTime, shiftEndTime);
                    // LatenessMin = Convert.ToInt64(presentTime.TotalMinutes - startTime.TotalMinutes);
                    if (LatenessMin <= 0)
                        LatenessMin = 0;
                    overTime = GetOverTime(duration, shiftStartTime, shiftEndTime, curInTime, curOutTime, _cutShiftTimes[count]);
                    if (overTime <= 0)
                        overTime = 0;
                    //Create datewiseAttendance entry if not exist
                    recCount = CheckDateWiseAttnData(_cutShiftTimes[count], empId);
                    if (recCount <= 0)
                    {
                        //Create datewiseAttendance entry if not exist
                        recordNumber = InsertDateWiseAttendenceEntry(empId, curInTime, curOutTime, LatenessMin, _cutShiftTimes[count], duration, overTime);
                    }
                    //Update FirstIntime and lateness if the entry is created newly
                    else
                    {
                        datewiseAttendenceDetailsDS = GetDatewiseAttendenceDetails(empId, _cutShiftTimes[count]);
                        totWorkedMin = Convert.ToInt32(datewiseAttendenceDetailsDS.Tables[0].Rows[0]["dtTotWorkedMin"].ToString());
                        UpdateFirstInTimeInDateWiseAttnData(curInTime, empId, _cutShiftTimes[count]);
                    }
                    overTime = GetOvetTimeFromDuration(totWorkedMin + duration, _cutShiftTimes[count], _cutShiftTimes[count + 1], curInTime, curOutTime, _cutShiftTimes[count]);
                    if (overTime <= 0)
                        overTime = 0;

                    UpdateOverTimeByRecordNo(recordNumber, overTime);
                    //Update workedHours
                    //Update shiftId in the table (new field)
                    UpdateInTimeConnectedAttnEntryByCardId(recordNumber, empId, curInTime);
                    //break the loop
                    break;
                }
            }
        }
    }

    /// <summary>
    /// Update FirstInTime
    /// </summary>
    /// <param name="inTime"></param>
    /// <param name="empId"></param>
    /// <param name="shiftDate"></param>
  protected static void UpdateFirstInTimeInDateWiseAttnData(DateTime inTime, long empId, DateTime shiftDate)
    {
        #region Fields
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion

        query = "UPDATE    DateWiseAttnData SET  dtFirstInTime ='" + inTime + "' where dtEmpID ='" + empId + "' and convert(varchar,dtDate,103)=convert(varchar,cast('" + shiftDate + "' as datetime),103)";
        int recCount = clsProcedure.ExecuteNonQuery(query);

    }

    /// <summary>
    /// Insert Data to DateWiseAttendence table
    /// </summary>
    /// <param name="empId"></param>
    /// <param name="curInTime"></param>
    /// <param name="curOutTime"></param>
    /// <param name="LatenessMin"></param>
    /// <param name="shiftDate"></param>
    /// <param name="duration"></param>
    /// <param name="overTime"></param>
    /// <returns></returns>
    protected static  long InsertDateWiseAttendenceEntry(long empId, DateTime curInTime, DateTime curOutTime, long LatenessMin, DateTime shiftDate, int duration, int overTime)
    {
        #region Fields
        object recordNo;
        long recNo = 0;
        string query = string.Empty;
        string query1 = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        query = "INSERT INTO DateWiseAttnData(dtDate, dtEmpID,dtFirstInTime, dtLastOutTime, dtLatenessMin,dtTotWorkedMin,dtHours,dtMinutes,dtOverTimeMin) VALUES     ('" + shiftDate.ToShortDateString() + "','" + empId + "','" + curInTime + "','" + curOutTime + "','" + LatenessMin + "','" + duration + "','" + duration / 60 + "','" + duration % 60 + "','" + overTime + "')" + "Select Scope_Identity()";
        recordNo = clsProcedure.ExecuteScalar(query);
        query1 = "INSERT INTO DateWiseAttnData(dtDate, dtEmpID,dtFirstInTime, dtLastOutTime, dtLatenessMin,dtTotWorkedMin,dtHours,dtMinutes,dtOverTimeMin) VALUES     ('" + shiftDate.ToShortDateString() + "','" + empId + "','" + curInTime + "','" + curOutTime + "','" + LatenessMin + "','" + duration + "','" + duration / 60 + "','" + duration % 60 + "','" + overTime + "')" + "Select Scope_Identity()";
       // InsertintoLogTable(query1);
        recNo = Convert.ToInt64(recordNo);
        return recNo;
    }

    /// <summary>
    /// Check datewise Attn Data
    /// </summary>
    /// <param name="shiftDate"></param>
    /// <param name="empID"></param>
    /// <returns></returns>
   protected static int CheckDateWiseAttnData(DateTime shiftDate, long empID)
    {
        #region Fields
        string query = string.Empty;
        int recCount = 0;
        Procedure clsProcedure = new Procedure();
        DataSet dateWiseDS = new DataSet();
        #endregion

        query = "SELECT     dtDate FROM  DateWiseAttnData where dtEmpID='" + empID + "' and convert(varchar,dtDate,103)=Convert(varchar,cast('" + shiftDate + "' as datetime),103)";
        dateWiseDS = clsProcedure.ExecuteQuery(query);
        recCount = dateWiseDS.Tables[0].Rows.Count;
        return recCount;
    }
  
    /// <summary>
    /// 
    /// </summary>
    /// <param name="empId"></param>
    /// <returns></returns>

   protected static  List<DateTime> GetShiftTimeByEmpId(int empId)
    {
        #region Fields
        Procedure clsProcedure = new Procedure();
        string query = string.Empty;
        DataSet shiftDetailsDS = new DataSet();
        List<DateTime> shiftDetails = new List<DateTime>();
        #endregion

        query = "SELECT ShStartTime,ShEndTime  FROM ShiftDetailsCardsView where EmpId='" + empId + "'";
        SqlContext.Pipe.Send(query);
        shiftDetailsDS = clsProcedure.ExecuteQuery(query);
        if (shiftDetailsDS.Tables[0].Rows.Count > 0)
        {
            for (int count = 0; count < shiftDetailsDS.Tables[0].Rows.Count; count++)
            {
                shiftDetails.Add(Convert.ToDateTime(shiftDetailsDS.Tables[0].Rows[count]["ShStartTime"].ToString()));
                shiftDetails.Add(Convert.ToDateTime(shiftDetailsDS.Tables[0].Rows[count]["ShEndTime"].ToString()));

            }
        }
        return shiftDetails;
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="curInTime"></param>
    /// <returns></returns>
   protected static List<DateTime> PrepareShiftTimes(DateTime curInTime, long empId)
    {
        DateTime shiftStartTime = Convert.ToDateTime(null);
        DateTime shiftEndTime = Convert.ToDateTime(null);

        TimeSpan presentTime = TimeSpan.Zero;
        List<DateTime> curShiftTimes = new List<DateTime>();
        List<DateTime> _shiftTimes = new List<DateTime>();
        _shiftTimes = GetShiftDetailsByEmpId(empId);
        presentTime = curInTime.TimeOfDay;
        //take datepart of curInTime in curDate


        for (int count = 0; count < _shiftTimes.Count; count = count + 2)
        //Start Loop for entries in _shiftTimes
        {
            DateTime shiftTime = Convert.ToDateTime(_shiftTimes[count].ToString());
            TimeSpan startTime = shiftTime.TimeOfDay;

            DateTime endTime = Convert.ToDateTime(_shiftTimes[count + 1].ToString());
            TimeSpan sEndTime = endTime.TimeOfDay;

            string curInTimeDate = curInTime.Date.ToShortDateString();
            DateTime inTimeDate = Convert.ToDateTime(curInTimeDate);


            //Shift overlapped
            if (startTime > sEndTime)
            {
                //if (presentTime <= startTime)
                //{
                //        DateTime previosDate = inTimeDate.AddDays(-1);
                //        shiftStartTime = previosDate.Add(startTime);
                //        shiftEndTime = inTimeDate.Add(sEndTime);
                // }
                if (presentTime >= startTime)
                {
                    shiftStartTime = inTimeDate.Add(startTime);
                    shiftEndTime = inTimeDate.Add(sEndTime);
                }
                else
                {
                    if (presentTime <= sEndTime)
                    {
                        DateTime shiftEnd = inTimeDate.AddDays(1);
                        shiftStartTime = inTimeDate.Add(startTime);
                        shiftEndTime = shiftEnd.Add(sEndTime);
                        //shiftStartTime = curInTime.Subtract(new TimeSpan(1, 0, 0, 0));
                        //shiftEndTime = inTimeDate.Add(new TimeSpan(1, 0, 0, 0));
                    }
                }
                if (presentTime <= startTime)
                //InTime is AM
                {
                    DateTime shiftEnd = inTimeDate.AddDays(1);
                    shiftStartTime = inTimeDate.Add(startTime);
                    shiftEndTime = shiftEnd.Add(sEndTime);
                }
            }
            else
            {
                shiftStartTime = inTimeDate.Add(startTime);
                shiftEndTime = inTimeDate.Add(sEndTime);
            }

            curShiftTimes.Add(shiftStartTime);
            curShiftTimes.Add(shiftEndTime);
            //curShifttime = new _shiftTime;
            //set start and end time calculated above

        }
        return curShiftTimes;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="empId"></param>
    /// <returns></returns>
   protected static List<DateTime> GetShiftDetailsByEmpId(long empId)
    {
        #region Fields
        Procedure clsCommonClass = new Procedure();
        DataSet shiftTimeDS = new DataSet();
        List<DateTime> shiftDetails = new List<DateTime>();
        string query = string.Empty;
        #endregion

        query = "SELECT     ShiftDetails.ShName, ShiftDetails.ShStartTime, ShiftDetails.ShEndTime FROM  ShiftDetails INNER JOIN  ShiftMap ON ShiftDetails.ShId = ShiftMap.SmShiftId INNER JOIN   EmployDetails ON ShiftMap.SmEmpId = EmployDetails.EmpId WHERE     (ShiftMap.SmEmpId = '" + empId + "')";
        shiftTimeDS = clsCommonClass.ExecuteQuery(query);
        if (shiftTimeDS.Tables[0].Rows.Count > 0)
        {
            for (int count = 0; count < shiftTimeDS.Tables[0].Rows.Count; count++)
            {
                shiftDetails.Add(Convert.ToDateTime(shiftTimeDS.Tables[0].Rows[count]["ShStartTime"].ToString()));
                shiftDetails.Add(Convert.ToDateTime(shiftTimeDS.Tables[0].Rows[count]["ShEndTime"].ToString()));

            }
        }

        return shiftDetails;
    }

    /// <summary>
    /// Update OverTime by CardId
    /// </summary>
    /// <param name="cardId"></param>
    /// <param name="nearestShift"></param>
    /// <param name="overTime"></param>
    protected static void UpdateOverTimeByCardID(long cardId, DateTime nearestShift, int overTime)
    {
        #region Fields
        string query;
        int count = 0;
        Procedure clsProcedure = new Procedure();
        #endregion
        try
        {
            query = "UPDATE    DateWiseAttnData SET  dtOverTimeMin ='" + overTime + "' WHERE dtEmpID='" + cardId + "' and  dtStatus=0 and Convert(varchar,dtDate,101)=Convert(varchar,cast('" + nearestShift + "' as datetime),101)";
            // SqlContext.Pipe.Send(query);
            //Convert(varchar,dtDate,101)=Convert(varchar,cast('"+ inTime +"' as datetime),101)";
            count = clsProcedure.ExecuteNonQuery(query);
            // SqlContext.Pipe.Send("No of rows Updated(DateWiseAttn table)" + ":" + count);
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="totalWorkedTime"></param>
    /// <param name="shiftStartTime"></param>
    /// <param name="shiftEndTime"></param>
    /// <param name="intime"></param>
    /// <param name="outTime"></param>
    /// <param name="nearestshiftdate"></param>
    /// <returns></returns>
    protected static  int GetOvetTimeFromDuration(int totalWorkedTime, DateTime shiftStartTime, DateTime shiftEndTime, DateTime intime, DateTime outTime, DateTime nearestshiftdate)
    {
        #region Fields
        int OvertIme = 0;
        TimeSpan duration = TimeSpan.Zero;
        TimeSpan checkAM = TimeSpan.Zero;
        int totShiftDuration = 0;
        #endregion

        // SqlContext.Pipe.Send("overtime");
        DateTime presenttime = Convert.ToDateTime(nearestshiftdate.ToShortDateString());
        TimeSpan startTime = intime.TimeOfDay;
        DateTime endtime = Convert.ToDateTime(outTime.ToShortDateString());
        DateTime check = DateTime.Parse("12:00");
        checkAM = check.TimeOfDay;
        //SqlContext.Pipe.Send("date1" + date1.ToString());
        //if ((shiftStartTime > checkAM && shiftEndTime < checkAM) || (shiftStartTime < checkAM && shiftEndTime < checkAM))
        //{
        //date2 = date1.AddDays(1);
        //DateTime shiftEnd = Convert.ToDateTime(date2.ToShortDateString());
        //date2 = shiftEnd.Add(shiftEndTime);
        // SqlContext.Pipe.Send("date2" + date2.ToString());

        //}
        //else
        //{
        //    date2 = presenttime.Add(shiftEndTime);
        //    //SqlContext.Pipe.Send("date2222" + date2.ToString());

        //}
        duration = shiftEndTime - shiftStartTime;
        //SqlContext.Pipe.Send("duration" + duration.ToString());
        totShiftDuration = Convert.ToInt32(duration.TotalMinutes);
        //SqlContext.Pipe.Send(totShiftDuration.ToString());
        OvertIme = totalWorkedTime - totShiftDuration;
        return OvertIme;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="recordNo"></param>
    /// <param name="overTime"></param>
    protected static void UpdateOverTimeByRecordNo(long recordNo, int overTime)
    {
        int count = 0;
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();

        query = "Update DateWiseAttnData set dtOverTimeMin='" + overTime + "'  where RecordNo='" + recordNo + "'";
        count = clsProcedure.ExecuteNonQuery(query);
    }


 
    protected static bool UpdateLogTable(long uniqId)
    {
        #region Fields
        bool status;
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        #endregion
        query = "UPDATE    TestCLRPerfomance SET  EndTime =getdate() where UniqId='" + uniqId + "'";
        string query1 = "UPDATE    TestCLRPerfomance SET TimeInMs =datediff(ms,StartTime,EndTime) where UniqId='" + uniqId + "'";
        SqlContext.Pipe.Send(query);
        SqlContext.Pipe.Send(query1);
        int count= clsProcedure.ExecuteNonQuery(query);
        int count1 = clsProcedure.ExecuteNonQuery(query1);
        if (count > 0)
            status = true;
        else
            status = false;
        return status;
    }

    protected static DataSet GetTransactionLogData()
    {
        #region Fields
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        DataSet transactionLogDS=new DataSet();
        #endregion

        try
        {
            query = "SELECT     distinct trsCardId from TransactionLog  WHERE (trsStatus = 0) ORDER BY trsCardId";
            transactionLogDS = clsProcedure.ExecuteQuery(query);
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return transactionLogDS;
    }

    protected static DataSet GetUserDataSetByUniqID(long cardId)
    {
         #region Fields
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        DataSet userDS=new DataSet();
        #endregion

        try
        {
            query = "SELECT     trsTransID, trsTimeStamp, trsInOut, trsCardId, trsStatus FROM TransactionLog WHERE (trsStatus = 0) and (trsCardId='"+cardId+"') ORDER BY trsCardId, trsTimeStamp";
            userDS = clsProcedure.ExecuteQuery(query);
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return userDS;
    }

    protected static int DeleteTransactionLogByTransactionTime(long cardId, DateTime trsTimeStamp)
    {
         #region Fields
        string query = string.Empty;
        Procedure clsProcedure = new Procedure();
        int count=0;
        #endregion

        try
        {
            query = " DELETE FROM TransactionLog where trsCardId='" + cardId + "' and trsTimeStamp='" + trsTimeStamp + "' and trsStatus=0 and trsInOut=2";
            count = clsProcedure.ExecuteNonQuery(query);
        }
         catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error executing SQL statement: " + ex.Message);
        }
        return count;
    }
    #endregion
};




